package com.hushan.elevator.dao;

/**
 * @Author: zzy
 * @Date: 2024/10/22 22:52
 * @Title:
 * @Version:
 * @Package: com.hushan.elevator.dao
 */
import com.hushan.elevator.model.DeviceInfo;
import jakarta.transaction.Transactional;
import org.jetbrains.annotations.NotNull;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface DeviceInfoRepository extends JpaRepository<DeviceInfo, Integer> {
    @Transactional
    @Modifying
    @Query(value = "update device_info set is_deleted = 1 where device_id = :deviceId;",nativeQuery = true)
    void deleteById(@NotNull Integer deviceId);

    @Query(value = "select * from device_info where dept_id = :deptId and is_deleted=false;",nativeQuery = true)
    List<DeviceInfo> findByDeptId(Integer deptId);

    @Query(value="select * from device_info d " +
            "INNER JOIN device_mnt_person dp on d.device_id = dp.device_id " +
            "INNER JOIN mnt_person p on p.mnt_user_id = dp.mnt_user_id " +
            "where p.mnt_user_id = :mntPersonId and d.is_deleted = false",
            nativeQuery = true)
    // get device info by mnt person
    List<DeviceInfo> findByMntPerson(Integer mntPersonId);

    @Query(value="select count(d.dept_id) from device_info d " +
            "INNER JOIN device_mnt_person dp on d.device_id = dp.device_id " +
            "INNER JOIN mnt_person p on p.mnt_user_id = dp.mnt_user_id " +
            "where p.mnt_user_id = :mntPersonId and d.is_deleted = false",
            nativeQuery = true)
    // get the mnt person can mnt device number
    int findCountByMntPerson(Integer mntPersonId);

    @Query(value = "select \n" +
            "devi.*, \n" +
            "d.name,d.address,d.parent_id,d.ancestors \n" +
            "from \n" +
            "device_info AS devi inner join\n" +
            "auth_dept as d \n" +
            "ON devi.dept_id = d.dept_id \n" +
            "WHERE d.is_deleted=0 and devi.is_deleted=0 and (FIND_IN_SET(:deptId,d.ancestors) or d.deptId=:deptId);\n",nativeQuery = true)
    // get the dept and the children dept devices
    List<DeviceInfo> findChildrenDevicesByDept(int deptId);

    DeviceInfo findBySerialNumber(String serialNumber);

    // 可以根据设备状态查询设备信息
    List<DeviceInfo> findByStatus(Integer status);

    // 可以根据创建时间范围查询设备信息
    List<DeviceInfo> findByCreatedTimeBetween(LocalDateTime start, LocalDateTime end);

    @Modifying
    @Transactional
    @Query("update DeviceInfo set status = ?1 where serialNumber = ?2")
    void updateOnlineBySerialNumber(int status, String serialNumber);
}